# -*- coding: utf-8 -*-

import pymysql
import datetime
from pymysql.err import Error
import logging


class MySQLHelperManual(object):
    """
        MYSQL数据库对象，直连，关闭
        获取连接对象：conn = Mysql.getConn()
        释放连接对象;conn.close()
    """

    logger = logging

    def __init__(self, **kwargs):
        """
        数据库构造函数，从连接池中取出连接，并生成操作游标
        """
        self.logger.warning("mysql config %s" % str(kwargs))
        self.mysql_config = kwargs

    def connect(self):
        try:
            conn = pymysql.connect(charset='utf8',
                                   **self.mysql_config)
            return conn
        except Error as ex:
            logging.error('MySQL connections failed: {0}.'.format(ex))

    def __execute(self, conn, sql, param=None):
        """
        执行sql
        :return:
        """
        try:
            cursor = conn.cursor()
            if param:
                r = cursor.execute(sql, param)
            else:
                r = cursor.execute(sql)
            return r, cursor
        except Exception as error:
            logging.error('Common query error:{0}'.format(error))

    def find_all(self, conn, sql, param=None):
        """
        @summary: 执行查询，并取出所有结果集
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result list/boolean 查询到的结果集
        """
        count, cursor = self.__execute(conn, sql=sql, param=param)
        if count > 0:
            rows = cursor.fetchall()
            dict_rows = []
            for row in rows:
                dict_item = self.sql_row_to_dict(cursor=cursor, row=row)
                dict_item = self.__dict_datetime_obj_to_str(dict_item)
                dict_rows.append(dict_item)
            return dict_rows
        else:
            result = False
        return result

    def find_one(self, conn, sql, param=None):
        """
        @summary: 执行查询，并取出第一条
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result list/boolean 查询到的结果集
        """
        count, cursor = self.__execute(conn, sql=sql, param=param)
        if count > 0:
            row = cursor.fetchone()
            dict_item = self.sql_row_to_dict(cursor=cursor, row=row)
            dict_item = self.__dict_datetime_obj_to_str(dict_item)
            return dict_item
        else:
            result = False
        return result

    def find_many(self, conn, sql, num, param=None):
        """
        @summary: 执行查询，并取出num条结果
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param num:取得的结果条数
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result list/boolean 查询到的结果集
        """
        count, cursor = self.__execute(conn, sql=sql, param=param)
        if count > 0:
            rows = cursor.fetchmany(num)
            dict_rows = []
            for row in rows:
                dict_item = self.sql_row_to_dict(cursor=cursor, row=row)
                dict_item = self.__dict_datetime_obj_to_str(dict_item)
                dict_rows.append(dict_item)
            return dict_rows
        else:
            result = False
        return result

    def insertMany(self, conn, sql, values):
        """
        @summary: 向数据表插入多条记录
        @param sql:要插入的ＳＱＬ格式
        @param values:要插入的记录数据tuple(tuple)/list[list]
        @return: count 受影响的行数
        """
        try:
            cursor = conn.cursor()
            rows = cursor.executemany(sql, values)
            conn.commit()
            return rows
        except Exception as e:
            logging.warning(e)

    def insert_many(self, conn, table, attrs, values):
        """
         插入大量数据
         attrs:[id,name,...]
         values:[[1,'jack'],[2,'rose']]
        :param table:表名
        :param attrs:字段名
        :param values: 字段值list[list]
        :return:
        """
        values_sql = ['%s' for v in attrs]
        attrs_sql = '(' + ','.join(attrs) + ')'
        values_sql = ' values(' + ','.join(values_sql) + ')'
        sql = 'insert into %s' % table
        sql = sql + attrs_sql + values_sql
        # print '_insertMany:' + sql
        try:
            # print sql
            # print values
            for i in range(0, len(values), 20000):
                rows = self.insertMany(conn, sql, values[i:i + 20000])
        except Error as e:
            error = '_insertMany executemany failed! ERROR (%s): %s' % (e.args[0], e.args[1])
            logging.error(error)

    def insert_one(self, conn, sql, value=None):
        """
        @summary: 向数据表插入一条记录
        @param sql:要插入的ＳＱＬ格式
        @param value:要插入的记录数据tuple/list
        @return: insertId 受影响的行数
        """
        _, cursor = self.__execute(conn, sql=sql, param=value)
        last_id = int(cursor.lastrowid)
        conn.commit()
        return last_id

    def insert_one_without_tranaction(self, conn, sql, value=None):
        """
        @summary: 向数据表插入一条记录
        @param sql:要插入的ＳＱＬ格式
        @param value:要插入的记录数据tuple/list
        @return: insertId 受影响的行数
        """
        _, cursor = self.__execute(conn, sql=sql, param=value)
        last_id = int(cursor.lastrowid)
        return last_id

    def __query(self, conn, sql, param=None):
        count, cursor = self.__execute(conn, sql=sql, param=param)
        return count

    def update(self, conn, sql, value=None):
        """
        @summary: 更新数据表记录
        @param sql: ＳＱＬ格式及条件，使用(%s,%s)
        @param value: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """
        num = self.__query(conn, sql, param=value)
        conn.commit()
        return num

    def update_without_tranaction(self, conn, sql, value=None):
        num = self.__query(conn, sql, value)
        return num

    def update_db(self, conn, data_table, data, find_dict):
        """
        :param conn: 使用的连接
        :param data_table: 要更新的表名
        :param data: 要更新的值 dict
        :param find_dict: 更新的where条件
        :return: count 受影响的行数
        """
        if not isinstance(data, dict):
            return False
        find_keys = list(find_dict.keys())
        keys = list(data.keys())
        update_sql = "update `" + data_table + "` set " + ", ".join(["`" + i + "`" + "= %s" for i in keys]) \
                     + " where " + " and ".join(["`" + i + "`" + "= %s" for i in find_keys])
        # print('update')
        num = self.update(conn, update_sql, value=list(data.values()) + list(find_dict.values()))
        conn.commit()
        return num

    def update_dbs(self, conn, data_table, data, find_dict):
        """
        :param conn: 使用的连接
        :param data_table: 要更新的表名
        :param data: 要更新的值 dict
        :param find_dict: 更新的where条件
        :return: count 受影响的行数
        """
        if not isinstance(data, dict):
            return False
        find_keys = list(find_dict.keys())
        keys = list(data.keys())
        update_sql = "update `" + data_table + "` set " + ", ".join(["`" + i + "`" + "= %s" for i in keys]) \
                     + " where " + " and ".join(["`" + i + "`" + "= %s" for i in find_keys])
        # print('update')
        num = self.update_without_tranaction(conn, update_sql, value=list(data.values()) + list(find_dict.values()))
        return num

    def delete(self, conn, sql, param=None):
        """
        @summary: 删除数据表记录
        @param sql: ＳＱＬ格式及条件，使用(%s,%s)
        @param param: 要删除的条件 值 tuple/list
        @return: count 受影响的行数
        """
        num = self.__query(conn, sql, param)
        conn.commit()
        return num

    def save_db(self, conn, data, data_table, find_dict, duplicate_update=False):
        """
        写入数据库
        :param data : 字典格式, key为插入的字段 value 为 插入的值
        :return:
        """
        if not isinstance(data, dict):
            return False
        find_keys = list(find_dict.keys())
        is_find = False
        if find_dict:
            # 数据存在
            sql = "select count(1) from " + data_table + " where " + " and ".join(["`" + i + "`" + "= %s" for i in find_keys])
            item = self.find_one(conn, sql, list(find_dict.values()))
            if item and item['count(1)'] > 0:
                is_find = True

        keys = list(data.keys())
        if not is_find:
            insert_sql = "insert `" + data_table + "`" + "(" + ",".join(["`" + i + "`" for i in keys]) + \
                         ") value (" + ",".join(["%s" for i in keys]) + ")"
            # print('insert')
            self.insert_one(conn, insert_sql, value=list(data.values()))
        elif duplicate_update:
            update_sql = "update `" + data_table + "` set " + ", ".join(["`" + i + "`" + "= %s" for i in keys]) \
                         + " where " + " and ".join(["`" + i + "`" + "= %s" for i in find_keys])
            # print('update')
            self.update(conn, update_sql, value=list(data.values()) + list(find_dict.values()))
        else:
            pass
        return True

    def save_dbs(self, conn, data, data_table, find_dict, duplicate_update=False):
        """

        :param conn:
        :param data:
        :param data_table:
        :param duplicate_update:
        :return:
        """
        if not isinstance(data, dict):
            return False
        find_keys = list(find_dict.keys())
        is_find = False
        if find_dict:
            # 数据存在
            sql = "select count(1) from " + data_table + " where " + " and ".join(["`" + i + "`" + "= %s" for i in find_keys])
            item = self.find_one(conn, sql, list(find_dict.values()))
            if item and item['count(1)'] > 0:
                is_find = True

        keys = list(data.keys())
        if not is_find:
            insert_sql = "insert `" + data_table + "`" + "(" + ",".join(["`" + i + "`" for i in keys]) + \
                         ") value (" + ",".join(["%s" for i in keys]) + ")"
            self.insert_one_without_tranaction(conn, insert_sql, value=list(data.values()))
        elif duplicate_update:
            update_sql = "update `" + data_table + "` set " + ", ".join(["`" + i + "`" + "= %s" for i in keys]) \
                         + " where " + " and ".join(["`" + i + "`" + "= %s" for i in find_keys])
            self.update_without_tranaction(conn, update_sql, value=list(data.values()) + list(find_dict.values()))
        else:
            pass
        return True

    @staticmethod
    def sql_row_to_dict(cursor=None, row=None):
        """
        Sql result row to dict
        :param cursor:
        :param row:
        :return:
        """

        try:
            result = dict()
            columns = [desc[0] for desc in cursor.description]
            for k, v in zip(columns, row):
                result[k] = v
            return result
        except Error as error:
            logging.error('sql_row_to_dict error: {0}.'.format(error))

    def get_find_dict(self, item, keys):
        find = {}
        for key in keys:
            find[key] = item[key]
        return find

    @staticmethod
    def get_insert_sql(table, data):
        keys = list(data.keys())
        values = list(data.values())
        insert_sql = "insert `" + table + "`" + "(" + ",".join(["`" + i + "`" for i in keys]) + \
                     ") value (" + ",".join(["%s" for i in keys]) + ")"
        return insert_sql, values

    @staticmethod
    def get_update_sql(table, data, find):
        find_keys = list(find.keys())
        keys = list(data.keys())
        update_sql = "update `" + table + "` set " + ", ".join(["`" + i + "`" + "= %s" for i in keys]) \
                     + " where " + " and ".join(["`" + i + "`" + "= %s" for i in find_keys])
        return update_sql, list(data.values()) + list(find.values())

    @staticmethod
    def __dict_datetime_obj_to_str(result_dict):
        """把字典里面的datatime对象转成字符串，使json转换不出错"""
        if result_dict:
            result_replace = {k: v.__str__() for k, v in list(result_dict.items()) if isinstance(v, datetime.datetime)}
            result_dict.update(result_replace)
        return result_dict
